﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections.ObjectModel;
using PM.Entity;
using PM.Common;
using System.Data.OracleClient;
using System.Data;

namespace PM.Services
{
    public class TaxServices
    {
        private static Tax GetTaxFromDataReader(IDataReader dataReader)
        {
            Tax retVal = null;
            if (dataReader != null && !dataReader.IsClosed)
            {
                retVal = new Tax();
                retVal.TaxId = dataReader["TAX_ID"].ToString();
                retVal.Decription = dataReader["DECRIPTION"].ToString();
                double percentage = 0;
                if(double.TryParse(dataReader["PERCENTAGE"].ToString(), out percentage))
                    retVal.Percentage = percentage;                
            }
            return retVal;
        }

        public static Tax GetTaxById(string taxId)
        {
            Tax retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT * FROM TAX WHERE TAX_ID = '" + taxId + "'";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader.Read())
                    {
                        retVal = GetTaxFromDataReader(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static TaxCollection GetTaxCollection()
        {
            TaxCollection retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT * FROM TAX";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new TaxCollection();
                    while (dataReader.Read())
                    {
                        Tax item = GetTaxFromDataReader(dataReader);
                        if (item != null)
                            retVal.Add(item);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static DataTable GetTaxCollectionDataTable()
        {
            DataTable retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT * FROM TAX";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static void Add(Tax tax)
        {
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "INSERT INTO TAX(TAX_ID,DECRIPTION,PERCENTAGE)" +
                                  "values(:v1,:v2,:v3)";
                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = tax.TaxId;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = tax.Decription;
                cmd.Parameters.Add(":v3", OracleType.VarChar).Value = tax.Percentage;

                cmd.Connection = con;
                cmd.ExecuteNonQuery();

            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

        public static void Delete(string taxId)
        {
            OracleConnection con = new OracleConnection();
            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "DELETE FROM BROKER WHERE TAX_ID = '" + taxId + "'";
                cmd.Connection = con;

                cmd.ExecuteNonQuery();
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

        public static void Update(Tax tax)
        {
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "UPDATE TAX SET " +
                                  "DECRIPTION = :v2 ," +
                                  "PERCENTAGE  = :v3 " +
                                  "WHERE TAX_ID = :v1";
                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = tax.TaxId;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = tax.Decription;
                cmd.Parameters.Add(":v3", OracleType.Double).Value = tax.Percentage;

                cmd.Connection = con;
                cmd.ExecuteNonQuery();

            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }
    }
}
